package com.kh.pajx.sop.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.mapping.Array;
import org.springframework.stereotype.Repository;

import com.kh.pajx.sop.base.BaseDaoImpl;
import com.kh.pajx.sop.dao.ISchoolDao;
import com.kh.pajx.sop.domain.HsSchoolInfo;
/**
 * 学校信息持久化操作实现类
 * 
 * @author:	徐江涛
 * 2014年6月23
 */

@Repository
public class SchoolDaoImpl extends BaseDaoImpl<HsSchoolInfo> implements ISchoolDao {

	/**
	 *查询所有学校 
	 */
	@Override
	public List<HsSchoolInfo> findAllSchool(){
		List schoollist=getSession().createQuery(//
				"FROM HsSchoolInfo").list();
		return getSession().createQuery(//
				"FROM HsSchoolInfo").list();
	}

	@Override
	public HsSchoolInfo getSchoolById(String sclid) {
		// TODO Auto-generated method stub
		if (sclid == null) {
			return null;
		} else {
			return (HsSchoolInfo) getSession().get(HsSchoolInfo.class, sclid);
		}
	}
	/**
	 * 添加学校信息
	 * 
	 * @author lipengfei 2014/6/24
	 */
	public boolean addSchool(HsSchoolInfo hsSchoolInfo){
		save(hsSchoolInfo);
		return true;
	}

	@Override
	public List findByHql(String hql) {
		// TODO Auto-generated method stub
		List schoolList=(List)getSession().createQuery(hql).list();
		return schoolList;
	}

	@Override
	public List<HsSchoolInfo> getSchoolBySclIds(List<String> sclIds) {
		return getSession().createCriteria(HsSchoolInfo.class,"s").
				add(Restrictions.in("s.sclId", sclIds)).list();
	}
	@Override
	/**查询各地区开通学校数量*/
	public List getSclCountByArea() {
		String sql="select AREA_NAME,count(SCL_ID) from HS_SCHOOL_INFO scl left join "
				+ "SYS_AREA area on scl.AREA_CODE=area.AREA_CODE "
				+ "where scl.SCL_STATUS_FLAG='1' group by area.AREA_NAME";
		return this.getByJdbcSQL(sql);
	}
	/**统计在校生、毕业生开通人数*/
		public List getstuInOutOpenNum(){
			String sql="select  sum(t.insch_open_num),sum(t.outsch_open_num) from yw_rpt_sch_daydetail t where t.yw_enddate=to_char(sysdate - 1,'yyyymmdd')";
			
			return this.getByJdbcSQL(sql);
		}
	/**--统计在校生各套餐分布情况*/
	public List gettaocanNum(){
		
		String sql="select  sum(t.a_users),sum(t.b_users),sum(t.c_users),sum(t.d_users) from yw_rpt_sch_daydetail t where t.yw_enddate=to_char(sysdate - 1,'yyyymmdd')";
		
		return this.getByJdbcSQL(sql);
	}
	/**在校生人数、在校生开通人数、毕业生人数、毕业生开通人数 */
	public List getstuAllNum(){
		
		String sql="select sum(t.insch_num),sum(t.insch_open_num),sum(t.outsch_num),sum(t.outsch_open_num) from yw_rpt_sch_daydetail t where t.yw_enddate=to_char(sysdate - 1,'yyyymmdd')";
		
		return this.getByJdbcSQL(sql);
	}
	/**各部门截止当前开通人数*/
	public List getdeptOpenNum(){
		
		String sql="select  t.sale_dept_name,sum(t.insch_open_num+t.outsch_open_num) from yw_rpt_sch_daydetail t where t.yw_enddate=to_char(sysdate - 1,'yyyymmdd') group by t.sale_dept_name";
		
		return this.getByJdbcSQL(sql);
	}
		
		
		
		
	
	
	
	
	/**查询市场各部门开通学校数量*/
	@Override
	public List getSclCountByDept() {
		/*String sql="select Dept_Name,count(*) from SYS_DEPARTMENT dept "
				+ "left join Sys_Inside_User in_user on In_User.Dept_Code=Dept.Dept_Code "
				+ "left join HS_SCHOOL_RELATION scl_rel on Scl_Rel.Sale_User_Id=In_User.Isu_Id "
				+ "left join Hs_School_Info scl on Scl_Rel.Scl_Id=Scl.Scl_Id "
				+ "where dept.PARENT_CODE='1001' and Scl_Rel.Relation_Status_Flag='1' "
				+ "and Scl.Scl_Status_Flag='1' and in_user.ISU_GROUP_CODE='1100' "
				+ "group by Dept.Dept_Name";*/
		//区分市场一、二、三部
		String sql="select Dept_Name,count(*) from Sys_Department d "
				+ "left join Sys_Inside_User iu on D.Dept_Code=Iu.Dept_Code "
				+ "left join Hs_School_Info s on Iu.Isu_Id=S.Sale_User_Id "
				+ "where Iu.Isu_Group_Code='1100'  and S.Scl_Status_Flag='1' "
				+ "and D.Parent_Code='1001' group by d.Dept_Name";
		//不区分市场部门
		String sql2="select D.Dept_Name,count(*) from Hs_School_Info s "
				+ "inner join Sys_Inside_User i on S.Sale_User_Id=I.Isu_Id "
				+ "inner join Sys_Department d on I.Dept_Code=D.Dept_Code "
				+ "where I.Isu_Group_Code='1100' and S.Scl_Status_Flag='1' "
				+ "group by D.Dept_Name";
	
		List list=getByJdbcSQL(sql);
		if(list.size()==0){
			list=getByJdbcSQL(sql2);
		}
		return list;
	}
	/**按类型查询设备数量*/
	@Override
	public int getEquCount(String type) {
		String sql="select count(*) from HS_EQUIPMENT_INFO e where e.EQU_TYPE='"+type+"' and E.Equ_Status_Flag='1'";
		List<List<String>> list=this.getByJdbcSQL(sql);
		int count=Integer.parseInt(list.get(0).get(0));
		return count;
	}
	@Override
	public HsSchoolInfo findByName(String sclName) {
		// TODO Auto-generated method stub
		String hql="from HsSchoolInfo s where s.sclName=?";
		List<HsSchoolInfo> sclList=getSession().createQuery(//
				hql).setParameter(0, sclName).list();
		if(sclList.size()>0){
			return sclList.get(0);
		}else{
			return new HsSchoolInfo();
		}
	}
	
}
